<!DOCTYPE html><!--[if IE 8]><html class="no-js lt-ie9" lang="en" > <![endif]--><!--[if gt IE 8]><!--><html class="no-js" lang="en"><!--<![endif]--><head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  
  <title></title>
  

  
  <link href="https://fonts.googleapis.com/css?family=Lato:400,700|Roboto+Slab:400,700|Inconsolata:400,700" rel="stylesheet" type="text/css">

  <link rel="stylesheet" href="/v2/css/theme.css" type="text/css">
  <link href="/v2/css/custom.css" rel="stylesheet">

  <script src="//cdnjs.cloudflare.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
  <script src="//cdnjs.cloudflare.com/ajax/libs/modernizr/2.8.3/modernizr.min.js"></script>
  <script src="../../js/theme.js"></script>
  <script src="//cdnjs.cloudflare.com/ajax/libs/highlight.js/8.4/highlight.min.js"></script>
  <script src="../../search/main.js"></script>

  <style>
    body {font-size: 90%;}
    pre, code {font-size: 100%;}
    h3, h4, h5, h6 {color: #2980b9; font-weight: 300}
  </style> 
<meta name="robots" content="noindex"></head>

<body class="wy-body-for-nav" role="document">

  <div class="wy-grid-for-nav">

    
    <nav data-toggle="wy-nav-shift" class="wy-nav-side stickynav">
      <div class="wy-side-nav-search">
        <a href="/v2/" class="icon icon-home"> </a>
        <div role="search">
  <form id="rtd-search-form" class="wy-form" action="" method="get">
    <input type="text" name="q" placeholder="Search docs">
    <input type="hidden" name="check_keywords" value="yes">
    <input type="hidden" name="area" value="default">
  </form>
</div>
      </div>

      <div class="wy-menu wy-menu-vertical" data-spy="affix" role="navigation" aria-label="main navigation">
      <ul class="current">
   
  <span class="toctree-l0">Home</span>
  
  <li class="toctree-l1 ">
    <a class="" href="/v2/">Welcome</a>
    
  </li>
  
  <li class="toctree-l1 ">
    <a class="" href="/v2/imprint/">Imprint</a>
    
  </li>
     
  <span class="toctree-l0">Articles</span>
  
  <li class="toctree-l1 ">
    <a class="" href="/v2/articles/getting-started/">Getting started</a>
    
  </li>
  
  <li class="toctree-l1 ">
    <a class="" href="/v2/articles/heroku/">Heroku</a>
    
  </li>
  
  <li class="toctree-l1 ">
    <a class="" href="/v2/articles/express/">Usage with Express.JS</a>
    
  </li>
     
  <span class="toctree-l0">Docs</span>
  
  <li class="toctree-l1 ">
    <a class="" href="/v2/docs/installation/">Installation</a>
    
  </li>
  
  <li class="toctree-l1 current">
    <a class="current" href="/v2/docs/usage/">Usage</a>
    
    <ul>
      
    </ul>
    
  </li>
  
  <li class="toctree-l1 ">
    <a class="" href="/v2/docs/promises/">Promises</a>
    
  </li>
  
  <li class="toctree-l1 ">
    <a class="" href="/v2/docs/models/">Models</a>
    
  </li>
  
  <li class="toctree-l1 ">
    <a class="" href="/v2/docs/instances/">Instances</a>
    
  </li>
  
  <li class="toctree-l1 ">
    <a class="" href="/v2/docs/associations/">Associations</a>
    
  </li>
  
  <li class="toctree-l1 ">
    <a class="" href="/v2/docs/hooks/">Hooks</a>
    
  </li>
  
  <li class="toctree-l1 ">
    <a class="" href="/v2/docs/transactions/">Transactions</a>
    
  </li>
  
  <li class="toctree-l1 ">
    <a class="" href="/v2/docs/migrations/">Migrations</a>
    
  </li>
  
  <li class="toctree-l1 ">
    <a class="" href="/v2/docs/misc/">Misc</a>
    
  </li>
     
  <span class="toctree-l0">API</span>
  
  <li class="toctree-l1 ">
    <a class="" href="/v2/api/sequelize/">Sequelize</a>
    
  </li>
  
  <li class="toctree-l1 ">
    <a class="" href="/v2/api/model/">Model</a>
    
  </li>
  
  <li class="toctree-l1 ">
    <a class="" href="/v2/api/instance/">Instance</a>
    
  </li>
  
  <li class="toctree-l1 ">
    <a class="" href="/v2/api/associations/">Associations</a>
    
  </li>
  
  <li class="toctree-l1 ">
    <a class="" href="/v2/api/hooks/">Hooks</a>
    
  </li>
  
  <li class="toctree-l1 ">
    <a class="" href="/v2/api/promise/">Promise</a>
    
  </li>
  
  <li class="toctree-l1 ">
    <a class="" href="/v2/api/transaction/">Transaction</a>
    
  </li>
  
  <li class="toctree-l1 ">
    <a class="" href="/v2/api/datatypes/">Datatypes</a>
    
  </li>
  
  <li class="toctree-l1 ">
    <a class="" href="/v2/api/errors/">Errors</a>
    
  </li>
    
</ul>
      </div>
      &nbsp;
    </nav>

    <section data-toggle="wy-nav-shift" class="wy-nav-content-wrap">

      
      <nav class="wy-nav-top" role="navigation" aria-label="top navigation">
        <i data-toggle="wy-nav-top" class="icon icon-reorder"></i>
        <a href="/v2/"></a>
      </nav>

      
      <div class="wy-nav-content">
        <div class="rst-content">
          <div role="navigation" aria-label="breadcrumbs navigation">
  <ul class="wy-breadcrumbs">
    <li><a href="/v2/">Docs</a> »</li>
    <li>Usage</li>
      <li class="wy-breadcrumbs-aside">
        
      </li>
  </ul>
  <hr>
</div>
          <div role="main">
            <div class="section">
              <h2 id="basic-usage">Basic usage</h2>
<p>To get the ball rollin' you first have to create an instance of Sequelize. Use it the following way:</p>
<pre><code class="language-js">var sequelize = new Sequelize('database', 'username'[, 'password'])
</code></pre>
<p>This will save the passed database credentials and provide all further methods. Furthermore you can specify a non-default host/port:</p>
<pre><code class="language-js">var sequelize = new Sequelize('database', 'username', 'password', {
  host: "my.server.tld",
  port: 12345
})
</code></pre>
<p>If you just don't have a password:</p>
<pre><code class="language-js">var sequelize = new Sequelize('database', 'username')
// or
var sequelize = new Sequelize('database', 'username', null)
</code></pre>
<p>You can also use a connection string:</p>
<pre><code class="language-js">var sequelize = new Sequelize('mysql://user:pass@example.com:9821/dbname', {
  // Look to the next section for possible options
})
</code></pre>
<h2 id="options">Options</h2>
<p>Besides the host and the port, Sequelize comes with a whole bunch of options. Here they are:</p>
<pre><code class="language-js">var sequelize = new Sequelize('database', 'username', 'password', {
  // custom host; default: localhost
  host: 'my.server.tld',
&nbsp;
  // custom port; default: 3306
  port: 12345,
&nbsp;
  // custom protocol
  // - default: 'tcp'
  // - added in: v1.5.0
  // - postgres only, useful for heroku
  protocol: null,
&nbsp;
  // disable logging; default: console.log
  logging: false,
&nbsp;
  // max concurrent database requests; default: 50
  maxConcurrentQueries: 100,
&nbsp;
  // the sql dialect of the database
  // - default is 'mysql'
  // - currently supported: 'mysql', 'sqlite', 'postgres', 'mariadb'
  dialect: 'mysql',
&nbsp;
  // you can also pass any dialect options to the underlying dialect library
  // - default is empty
  // - currently supported: 'mysql', 'mariadb'
  dialectOptions: {
    socketPath: '/Applications/MAMP/tmp/mysql/mysql.sock',
    supportBigNumbers: true,
    bigNumberStrings: true
  },
&nbsp;
  // the storage engine for sqlite
  // - default ':memory:'
  storage: 'path/to/database.sqlite',
&nbsp;
  // disable inserting undefined values as NULL
  // - default: false
  omitNull: true,
&nbsp;
  // a flag for using a native library or not.
  // in the case of 'pg' -- set this to true will allow SSL support
  // - default: false
  native: true,
&nbsp;
  // Specify options, which are used when sequelize.define is called.
  // The following example:
  //   define: {timestamps: false}
  // is basically the same as:
  //   sequelize.define(name, attributes, { timestamps: false })
  // so defining the timestamps for each model will be not necessary
  // Below you can see the possible keys for settings. All of them are explained on this page
  define: {
    underscored: false
    freezeTableName: false,
    syncOnAssociation: true,
    charset: 'utf8',
    collate: 'utf8_general_ci',
    classMethods: {method1: function() {}},
    instanceMethods: {method2: function() {}},
    timestamps: true
  },
&nbsp;
  // similiar for sync: you can define this to always force sync for models
  sync: { force: true },
&nbsp;
  // sync after each association (see below). If set to false, you need to sync manually after setting all associations. Default: true
  syncOnAssociation: true,
&nbsp;
  // use pooling in order to reduce db connection overload and to increase speed
  // currently only for mysql and postgresql (since v1.5.0)
  pool: { maxConnections: 5, maxIdleTime: 30},
&nbsp;
  // language is used to determine how to translate words into singular or plural form based on the [lingo project](https://github.com/visionmedia/lingo)
  // options are: en [default], es
  language: 'en'
})
</code></pre>
<p><strong>Hint:</strong> You can also define a custom function for the logging part. Just pass a function. The first parameter will be the string that is logged.</p>
<h2 id="read-replication">Read replication</h2>
<p>Sequelize supports read replication, i.e. having multiple servers that you can connect to when you want to do a SELECT query. When you do read replication, you specify one or more servers to act as read replicas, and one server to act as the write master, which handles all writes and updates and propagates them to the replicas (note that the actual replication process is<strong>not </strong>handled by Sequelize, but should be set up in MySql).</p>
<pre><code class="language-js">var sequelize = new Sequelize('database', null, null, {
  dialect: 'mysql',
  port: 3306
  replication: {
    read: [
      { host: '8.8.8.8', username: 'anotherusernamethanroot', password: 'lolcats!' },
      { host: 'localhost', username: 'root', password: null }
    ],
    write: { host: 'localhost', username: 'root', password: null }
  },
  pool: { // If you want to override the options used for the read pool you can do so here
    maxConnections: 20,
    maxIdleTime: 30000
  },
})
</code></pre>
<p>If you have any general settings that apply to all replicas you do not need to provide them for each instance. In the code above, database name and port is propagated to all replicas. The same will happen for user and password, if you leave them out for any of the replicas. Each replica has the following options:<code>host</code>,<code>port</code>,<code>username</code>,<code>password</code>,<code>database</code></p>
<p>Sequelize uses a pool to manage connections to your replicas. The default options are:</p>
<pre><code class="language-js">{
  maxConnections: 10,
  minConnections: 0,
  maxIdleTime:    1000
}
</code></pre>
<p>If you want to modify these, you can pass pool as an options when instantiating Sequelize, as shown above.</p>
<p><strong>Note:</strong> Read replication only works for MySQL at the moment!</p>
<h2 id="dialects">Dialects</h2>
<p>With the release of Sequelize<code>v1&amp;period;6&amp;period;0</code>, the library got independent from specific dialects. This means, that you'll have to add the respective connector library to your project yourself. Version 1.7.0 stable has been released in bundles with the connector libraries (sequelize-mysql, sequelize-postgres etc.) but these bundles are not maintained, and will not be released for 2.0.0 upwards.</p>
<h3 id="mysql">MySQL</h3>
<p>In order to get Sequelize working nicely together with MySQL, you'll need to install<code>mysql&amp;commat;~2&amp;period;0&amp;period;0-alpha7</code>or higher. Once that's done you can use it like this:</p>
<pre><code class="language-js">var sequelize = new Sequelize('database', 'username', 'password', {
  // mysql is the default dialect, but you know...
  // for demo purporses we are defining it nevertheless :)
  // so: we want mysql!
  dialect: 'mysql'
})
</code></pre>
<p><strong>Note:</strong> You can pass options directly to dialect library by setting the
<code>dialectOptions</code> parameter. See <a href="/v2//docs/latest/usage#options">Options</a>
for examples (currently only mysql and mariadb are supported).</p>
<h3 id="mariadb">MariaDB</h3>
<p>For MariaDB compatibility you have to install the package <code>mariasql@0.1.20</code>, or higher.
The configuration needs to look like this:</p>
<pre><code class="language-js">var sequelize = new Sequelize('database', 'username', 'password', {
  dialect: 'mariadb'
})
</code></pre>
<h3 id="sqlite">SQLite</h3>
<p>For SQLite compatibility you'll need<code>sqlite3&amp;commat;~2&amp;period;1&amp;period;5</code>. Configure Sequelize like this:</p>
<pre><code class="language-js">var sequelize = new Sequelize('database', 'username', 'password', {
  // sqlite! now!
  dialect: 'sqlite',
&nbsp;
  // the storage engine for sqlite
  // - default ':memory:'
  storage: 'path/to/database.sqlite'
})
</code></pre>
<h3 id="postgresql">PostgreSQL</h3>
<p>The library for PostgreSQL is<code>pg&amp;commat;~2&amp;period;0&amp;period;0</code>. You'll just need to define the dialect:</p>
<pre><code class="language-js">var sequelize = new Sequelize('database', 'username', 'password', {
  // gimme postgres, please!
  dialect: 'postgres'
})
</code></pre>
<h2 id="executing-raw-sql-queries">Executing raw SQL queries</h2>
<p>As there are often use cases in which it is just easier to execute raw / already prepared SQL queries, you can utilize the function<code>sequelize&amp;period;query</code>.</p>
<p>Here is how it works:</p>
<pre><code class="language-js">// Arguments for raw queries
sequelize.query('your query', [, callee], [, options], [, replacements])

// Quick example
sequelize.query("SELECT * FROM myTable").success(function(myTableRows) {
  console.log(myTableRows)
})

// Callee is the model definition. This allows you to easily map a query
// to a predefined model for sequelizejs e.g:
sequelize
  .query('SELECT * FROM projects', Projects)
  .success(function(projects){
    // Each record will now be mapped to the project's DAO-Factory.
    console.log(projects)
  })


// Options is an object with the following keys:
sequelize
  .query('SELECT 1', null, {
    // A function (or false) for logging your queries
    // Will get called for every SQL query that gets send
    // to the server.
    logging: console.log,

    // If plain is true, then sequelize will only return the first
    // record of the result set. In case of false it will all records.
    plain: false,

    // Set this to true if you don't have a model definition for your query.
    raw: false
  })

// Note the second argument being null!
// Even if we declared a callee here, the raw: true would
// supersede and return a raw object.
sequelize
  .query('SELECT * FROM projects', null, { raw: true })
  .success(function(projects) {
    console.log(projects)
  })
</code></pre>
<p>Replacements in a query can be done in two different ways, either using
named parameters (starting with <code>:</code>), or unnamed, represented by a ?</p>
<p>The syntax used depends on the fourth argument passed to the function:</p>
<ul>
<li>If an array is passed, <code>?</code> will be replaced in the order that they appear in the array</li>
<li>If an object is passed, <code>:key</code> will be replaced with the keys from that object.
If the object contains keys not found in the query or vice verca, an exception
will be thrown.</li>
</ul>
<pre><code class="language-js">sequelize
  .query(
    'SELECT * FROM projects WHERE status = ?', null,
    { raw: true }, ['active']
  )
  .success(function(projects) {
    console.log(projects)
  })

sequelize
  .query(
    'SELECT * FROM projects WHERE status = :status ', null,
    { raw: true }, { status: 'active' }
  )
  .success(function(projects) {
    console.log(projects)
  })
</code></pre>
<p><strong>One note:</strong> If the attribute names of the table contain dots, the resulting objects will be nested:</p>
<pre><code class="language-js">sequelize.query('select 1 as `foo.bar.baz`').success(function(rows) {
  console.log(JSON.stringify(rows))

  /*
    [{
      "foo": {
        "bar": {
          "baz": 1
        }
      }
    }]
  */
})
</code></pre>
            </div>
          </div> 
          <footer>
  

  <hr>

  <div role="contentinfo">
    <p>
    <!-- Copyright etc -->
    </p>
  </div>

  Built with <a href="http://www.mkdocs.org">MkDocs</a>, using a <a href="https://github.com/snide/sphinx_rtd_theme">theme</a> provided by <a href="https://readthedocs.org">Read the Docs</a>.
</footer>
	  
        </div>
      </div>

    </section>

  </div>

<div class="rst-versions" role="note" style="cursor: pointer">
    <span class="rst-current-version" data-toggle="rst-current-version">
        
      <span><a href="/v2/" style="color: #fcfcfc;">« Previous</a></span>
      <span style="margin-left: 15px"><a href="/v2/" style="color: #fcfcfc">Next »</a></span>
    </span>
</div>

</body></html>